package com.infy.olt.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import com.infy.olt.dto.EmpDtl;
import com.infy.olt.dto.LoginDtl;

public class AccessDBConnection {
	Connection con;
    Statement stmt;
    ResultSet rs;

    public static void main(String[] args) {
    	AccessDBConnection connection = new AccessDBConnection();
        //step 1: load driver
        //loadDriver();

        //step 3: establish connection
        //makeConnection();
        //usePreparedStatement();
        //connection.getLoginDetails("V043068");
    	//connection.updateOncallDetails("Nothing", "Nothing", "08/16/2011 12:00", 
    		//	"08/17/2011 12:00", 24, "Operations", "EUSX", "Sumanth Kumar Charugundla");
        //retrieve data
        //retrieveData();

        //close all resources
        //closeAll();
    }

    // load a driver
    void loadDriver() {
        try {
            //step 2: Define connection URL
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
        }
    }

    // make a connection  step 3: establish connection
    void makeConnection() {
       //for how to set up data source name see below.
       String dsn = "TrackerTool";
       String url = "jdbc:odbc:" + dsn;
        try {
           con = DriverManager.getConnection(url, "", "");
           stmt = con.createStatement();
        }catch(SQLException ex) {
           System.err.println("database connection: " + ex.getMessage());
        }
    }

  
    //use PreparedStatement to precompile sql statement
    void usePreparedStatement() {
        try {
            PreparedStatement updateSales;
            String updateString = "select LD_LoginName from LoginDtl " +
                      "where LD_FileID like ?";
            updateSales = con.prepareStatement(updateString);
            updateSales.setString(1, "V029740");
            rs = updateSales.executeQuery();
            while(rs.next()){ 
            	System.out.println("Result : "+rs.getString("LD_LoginName"));
            }
        }catch(SQLException ex) {
            System.err.println("UsePreparedStatement: " + ex.getMessage());
       }
    }

    //retrieve data from table LoginDtl
    public LoginDtl getLoginDetails(String loginId) {
    	loadDriver();
        makeConnection();
    	LoginDtl loginDtl = null;
    	PreparedStatement prepStat;
       try {
           String gdta="select LD_FileID,LD_LoginName,LD_Password from LoginDtl " +
                      "where LD_FileID like ?";
           prepStat = con.prepareStatement(gdta);
           prepStat.setString(1, loginId);
           //step 6: process the results.
           rs = prepStat.executeQuery();
           while (rs.next()) {
                String fileId = rs.getString("LD_FileID");
                String loginName = rs.getString("LD_LoginName");
                String password = rs.getString("LD_Password");
                System.out.println(fileId + "   " + loginName +"  "+password);
                loginDtl = new LoginDtl();
                loginDtl.setFileId(fileId);
                loginDtl.setLoginName(loginName);
                loginDtl.setPassword(password);
           }
       }catch(SQLException ex) {
           System.out.println("RetrieveData: getLoginDetails" + ex.getMessage());
       }finally{
    	   closeAll();
       }
       return loginDtl;
     }
    
    //retrieve data from table EmpDtl
    public EmpDtl getEmployeeDetails(String empId) {
    	loadDriver();
        makeConnection();
    	EmpDtl empDtl = null;
    	PreparedStatement prepStat;
       try {
           String gdta="select ED_FileID,ED_ResourceName,ED_Role,ED_ReportingManager,ED_IBU,ED_Subcluster from EmpDtl " +
                      "where ED_FileID like ?";
           prepStat = con.prepareStatement(gdta);
           prepStat.setString(1, empId);
           //step 6: process the results.
           rs = prepStat.executeQuery();
           while (rs.next()) {
                String fileId = rs.getString("ED_FileID");
                String resourceName = rs.getString("ED_ResourceName");
                String role = rs.getString("ED_Role");
                String reportingMgr = rs.getString("ED_ReportingManager");
                String ibu = rs.getString("ED_IBU");
                String subCluster = rs.getString("ED_Subcluster");
                
                empDtl = new EmpDtl();
                empDtl.setFileID(fileId);
                empDtl.setResourceName(resourceName);
                empDtl.setRole(role);
                empDtl.setReportingMgr(reportingMgr);
                empDtl.setIbu(ibu);
                empDtl.setSubCluster(subCluster);
           }
       }catch(SQLException ex) {
           System.out.println("RetrieveData: getEmployeeDetails " + ex.getMessage());
       }finally{
    	   closeAll();
       }
       return empDtl;
     }

    //close statement and connection
    //step 7: close connection, etc. 
     void closeAll() {
         try {
            stmt.close();
            con.close();
         } catch(SQLException ex) {
            System.err.println("closeAll: " + ex.getMessage());
         }
    }

	public int updateOncallDetails(String onCallDetails, String remarks,
			String startDate, String endDate, String totalHrs, String subCluster, String ibu, String resourceName) {
		int result = 0;
		loadDriver();
        makeConnection();
    	PreparedStatement prepStat;
       try {
           String gdta = "update OCDtl set OC_OCDetail = ?  " +
           		", OC_Start_Date_Time = ?  " +
           		", OC_End_Date_Time = ?  " +
           		", OC_Total_Effort = ?  " +
           		", OC_Remarks = ? " +
           		"where OC_Resource like ?";
           
           prepStat = con.prepareStatement(gdta);
           prepStat.setString(1, onCallDetails);
           prepStat.setString(2, startDate);
           prepStat.setString(3, endDate);
           int totHrs = 0;
           if(totalHrs != null){
        	   totHrs = Integer.parseInt(totalHrs);
           }
           prepStat.setInt(4, totHrs);
           prepStat.setString(5, remarks);
           //prepStat.setString(6, subCluster);
           //prepStat.setString(7, ibu);
           prepStat.setString(6, resourceName);
           
           result = prepStat.executeUpdate();
           
       }catch(SQLException ex) {
           System.out.println("RetrieveData: getEmployeeDetails " + ex.getMessage());
           ex.printStackTrace();
       }finally{
    	   closeAll();
       }
       return result;
	}
}
